import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Date;
import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import java.util.logging.Logger;



/**
 * This Query Class must be used with a localhost with the appropriate database.
 * I have the database on my localhost and will demo the code. As a result, the
 * code will not work as it needs a database which you may not have and we do
 * not know how to send to you. If you like you can install xampp (windows) mamp
 * (mac) and it will set up a localhost complete with phpmyadmin where the
 * program can be tested.
 * 
 * @author Daranhdara Hun
 * @version 1.0
 */
public class MyQuery {
	
	private static final Logger myLogger = 
        Logger.getLogger("src.(default package).MyQuery");

    /**
     * ------------------------------------------------------------------------
     * -------------------------------------------- Use this method to search
     * for a patient by full name It will return a very long string with all
     * user attribute in the following order 1 = password 9 = name 2 = username
     * 10 = address 3 = pharmacy 11 = email 4 = birthdate 12 = suspend 5 =
     * cellphone 13 = ssn 6 = phone 7 = insurance 8 = gender After all 13
     * attributes are completed, the next 13 will be the attributes of the next
     * patient matching the name criteria.
     * 
     * @param name - patients name
     * @return List<User> - returns Arraylist of users
     */
    public List<User> searchUserByName(String name) 
    {
        try
        {
            final PreparedStatement statement = connect().prepareStatement(
                    "SELECT * FROM `patient` WHERE `name` LIKE " + "'" + name
                    + "' LIMIT 0 , 30");
            //        System.out.println("searchUserByName:line50 "+"SELECT * FROM `patient` WHERE `name` LIKE " + "'" + name
            //                    + "' LIMIT 0 , 30");
            // Creating a variable to execute query
            final ResultSet result = statement.executeQuery();
            // System.out.println(result);
            // constructs a new empty ArrayList

            final List<User> patientInfo = new ArrayList<User>();

            while (result.next()) {

                User tempUser = createUserBySSN(result.getInt(13));
                patientInfo.add(tempUser);

                /*
                 * getString returns the data. Below are patient attributes. 1 =
                 * password 9 = name 2 = username 10 = address 3 = pharmacy 11 =
                 * email 4 = birthdate 12 = suspend 5 = cellphone 13 = ssn 6 = phone
                 * 7 = insurance 8 = gender
                 */
            }

            return patientInfo;
        }
        catch(Exception e)
        {
        	myLogger.info("Search Failed");
            System.out.println("searchUserByName failled");
            return null;
        }
    }

    /**
     * Connects to the database. Use this method
     * @return Connection - returns a connection
     */
    private static Connection connect() 
    {
        try
        {
            // Accessing driver from the JAR file
            Class.forName("com.mysql.jdbc.Driver");

            // Creating a variable for the connection called "con"
            final Connection con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:8889/CS2340", "root", "root");
            // jdbc:mysql://localhost:8889/testdb --> database
            // DB user --> root
            // DB password --> root

            // insertStatement.executeUpdate("INSERT INTO names " +
            // "VALUES ('Jimmy', 'johns')");
            return con;
        }
        catch (Exception e)
        {
            System.out.println("Exception at line 115 of Query Class");
            return null;
        }

    }

    /**
     * Will deliver a string of attributes in the order they are being pulled from the database
     * 
     * @param ssn - social security number of a patient needed to access the
     *            database
     * @param attributeNumber - the index of the attribute you want to retrieve.
     * @return patientInfo - a string of all attributes for a particular patient
     */
    private static String getUserAttributes(int ssn, int attributeNumber)
    {
        String patientInfo = "";
        try
        {
            // System.out.println("Ran getUserAttributes");
            // create the query
            final PreparedStatement statement = connect().prepareStatement(
                    "SELECT * FROM patient WHERE ssn=" + ssn);

            // Creating a variable to execute query
            final ResultSet result = statement.executeQuery();



            while (result.next()) {
                switch (result.getMetaData().getColumnType(attributeNumber)) {
                case java.sql.Types.DATE:

                    DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");

                    // System.out.println("TESTING: " +
                    // result.getDate(attributeNumber));
                    patientInfo = formatter.format(result.getDate(attributeNumber));
                    // System.out.println("Date: " + patientInfo);
                    break;

                default:
                    patientInfo = result.getString(attributeNumber);
                    break;
                }
                // DateFormat formatter;
                // formatter = new SimpleDateFormat("yyyy-MM-dd");
                // System.out.println("Check a4:Before try " +formatter);
                /*
                 * patientInfo= result.getString(1) + " " + result.getString(2) +
                 * " " + result.getString(3) + " " + result.getString(4)+ " " +
                 * result.getString(5) + " " + result.getString(6) + " " +
                 * result.getString(7) + " " + result.getString(8)+ " " +
                 * result.getString(9) + " " + result.getString(10) + " " +
                 * result.getString(11) + " " + result.getString(12)+ " " +
                 * result.getString(13); getString returns the data. Below are
                 * patient attributes. 1 = password 9 = name 2 = username 10 =
                 * address 3 = pharmacy 11 = email 4 = birthdate 12 = suspend 5 =
                 * cellphone 13 = ssn 6 = phone 7 = insurance 8 = gender
                 */
            }
            return patientInfo;
        }
        catch(Exception e)
        {
        	myLogger.info("getUserAttributes failed");
            System.out.println("Exception: Returning emtpy string at MyQuery:getUserAttributes");
            return patientInfo;
        }
    }

    /**
     * Use this method ONLY if
     * you're accessing an attribute with a DATE DATATYPE.
     * 
     * @param ssn - Patient social security number
     * @param attributeNumber - number corresponding to the attribute number
     *             of a patient from the database
     *          Below are patient attributes: 
     *          1 = password 9 = name 2 = username 10 = address 3 = pharmacy 11 =
     *          email 4 = birthdate 12 = suspend 5 = cellphone 13 = ssn 6 = phone
     *          7 = insurance 8 = gender
     *
     * @return Date - returns the patients birthday.
     */
    private Date getPatientBday(int ssn, int attributeNumber) 
    {
        Date patientInfo = null;
        try
        {
            // System.out.println("Ran getPatientBday");
            // create the query
            final PreparedStatement statement = connect().prepareStatement(
                    "SELECT * FROM patient WHERE ssn=" + ssn);

            // Creating a variable to execute query
            final ResultSet result = statement.executeQuery();

            while (result.next()) {
                switch (result.getMetaData().getColumnType(attributeNumber)) {
                case java.sql.Types.DATE:
                    patientInfo = result.getDate(attributeNumber);
                    break;
                default:
                    patientInfo = null;
                    break;
                }

            }
            return patientInfo;
        }
        catch(SQLException s)
        {
        	myLogger.info("SQL exception");
            System.out.println("SQL Exception at MyQuery: getPatientBday and will return a null date");
            return patientInfo;
        }

    }

    /**
     * Saves Patient information
     * into database for the first time Employee will call this method to save
     * patient information
     * 
     * @param User - Employee must provide social security number
     */
    public void addUser(User user) {
        System.out.println("Ran addUser");
        Statement stmt = null;

        /**/
        final String password = user.getPassword();
        final String username = user.getUserName();
        final String pharmacy = user.getPharmacy();
        Date birthdate = user.getBirthDate();
        final Date date = new Date(1333333333);
        if (birthdate == null) {
            birthdate = date;
        }

        final String cellphone = user.getCellPhone();
        final String phone = user.getPhone();
        final String insurance = user.getInsurance();
        final Gender gender = user.getGender();
        final String name = user.getName();
        final String address = user.getAddress();
        final String email = user.getEmail();
        final boolean suspended = user.getSuspended();
        final int ssn = user.getSSN();
        /*
         * INSERT INTO `CS2340`.`patient` (`password`, `username`, `pharmacy`,
         * `birthdate`, `cellphone`, `phone`, `insurance`, `gender`, `name`,
         * `address`, `email`, `suspended`, `ssn`) VALUES ('c', 'd', NULL, NULL,
         * NULL, NULL, NULL, 'N/A', '', NULL, '', 'true', '');
         */

        try {
            stmt = connect().createStatement();

            stmt.executeUpdate("INSERT INTO `CS2340`.`patient` "
                    + "(`password`, `username`, `pharmacy`, `birthdate`, " +
                    "`cellphone`, `phone`, `insurance`, `gender`, `name`," +
                    " `address`, `email`, `suspended`, `ssn`) "
                    + "VALUES " + "('"
                    + password
                    + "', '"
                    + username
                    + "', '"
                    + pharmacy
                    + "', '"
                    + birthdate
                    + "', '"
                    + cellphone
                    + "', '"
                    + phone
                    + "', '"
                    + insurance
                    + "', '"
                    + gender
                    + "', '"
                    + name
                    + "', '"
                    + address
                    + "', '"
                    + email
                    + "', '"
                    + suspended
                    + "', '"
                    + ssn + "');");

            /*
             * getString returns the data. Below are patient attributes. 1 =
             * password 9 = name 2 = username 10 = address 3 = pharmacy 11 =
             * email 4 = birthdate 12 = suspend 5 = cellphone 13 = ssn 6 = phone
             * 7 = insurance 8 = gender
             */
        } catch (SQLException e) {
            System.out.println("SQLException problem");
        } catch (Exception e) {
            System.out.println("Exception problem");
            // TODO Auto-generated catch block
            // System.out.println("test");
            // e.printStackTrace();
        }

    }

    /**
     * Use this method to update a users information
     * 
     * @param ssn - must use SSN number when using this method
     */
    public void updateUser(User user, int ssn) 
    {

        // System.out.println("Ran updateUser");
        Statement stmt = null;

        final String password = user.getPassword();
        final String pharmacy = user.getPharmacy();
        final Date birthdate = user.getBirthDate();
        final String cellphone = user.getCellPhone();
        final String phone = user.getPhone();
        final String insurance = user.getInsurance();
        final Gender gender = user.getGender();
        final String name = user.getName();
        final String address = user.getAddress();
        final String email = user.getEmail();

        try 
        {
            stmt = connect().createStatement();
            stmt.executeUpdate("UPDATE  `CS2340`.`patient` SET  "
                    + "`password` =  '"
                    + password
                    + "', "
                    + "`username` =  '"
                    + password
                    + "', "
                    + "`pharmacy` =  '"
                    + pharmacy
                    + "',"
                    + "`birthdate` =  '"
                    + birthdate
                    + "',"
                    + "`cellphone` =  '"
                    + cellphone
                    + "',"
                    + "`phone` =  '"
                    + phone
                    + "',"
                    + "`insurance` =  '"
                    + insurance
                    + "',"
                    + "`gender` =  '"
                    + gender
                    + "',"
                    + "`name` =  '"
                    + name
                    + "',"
                    + "`address` =  '"
                    + address
                    + "',"
                    + "`email` =  '"
                    + email
                    + "'"
                    + "WHERE `patient`.`ssn` = " + ssn);
        } 
        catch(SQLException s)
        {
        	myLogger.info("Sql Exception");
            System.out.println("SQLException at MySql: Update User");
        }
        finally 
        {
            try {
                stmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }


    /**
     * STATIC METHOD - Deletes a user never to return again.
     * 
     * @param ssn - must specify user by SSN
     */
    public void deleteUser(int ssn)
    {
        // System.out.println("Ran deleteUser");
        Statement stmt = null;

        try 
        {
            stmt = connect().createStatement();

            final PreparedStatement test = connect().prepareStatement("SELECT * FROM `CS2340`.`patient` WHERE `patient`.`ssn` = "
                    + ssn);
            final ResultSet result = test.executeQuery();

            if (result.next())
            {
                try 
                {
                    stmt.executeUpdate("DELETE FROM `CS2340`.`patient` WHERE `patient`.`ssn` = " + ssn);
                } 
                finally 
                {
                    stmt.close();
                }
            }
        }
        catch (SQLException s)
        {
        	myLogger.info("SQL exception");
            System.out.println("SQLException at MySql: deleteUser");
        }
    }

    /**
     * Given a username and password, this method will check with the database
     * if there is a match, a user will be returned
     * @param password - Patient password in String format
     * @param username - Patient username in String format
     * @return User - User class with all variables gathered from database
     */
    public User authenticateUser(String username, String password)
    {
        // System.out.println("Ran authenticateUser");
        /* create the query */
        try
        {
            final PreparedStatement statement = connect().prepareStatement(
                    "SELECT * FROM `patient` WHERE `username` LIKE '" + username
                    + "'");

            /* Creating a variable to execute query */
            final ResultSet result = statement.executeQuery();

            String dbPassword = "";

            while (result.next()) {
                dbPassword = result.getString(1);

                /*
                 * getString returns the data. Below are patient attributes. 1 =
                 * password 9 = name 2 = username 10 = address 3 = pharmacy 11 =
                 * email 4 = birthdate 12 = suspend 5 = cellphone 13 = ssn 6 = phone
                 * 7 = insurance 8 = gender
                 */
            }

            /* Create User */
            if (password.equals(dbPassword)) /* Password Success */
            {
                return createUser(username);
            } else /* Password Fail */{
                return null;
                /*return createUser("No User");*/
            }
        }
        catch(Exception e)
        {
            System.out.println("Exception at MyQuery: authenticateUser");
            return null;
        }
    }



    /**
     * Use this method only after the authenticateUser method has passed 
     * @param user
     * @param role (1 = patient, 2 = nurse, 3 = doctor, 4 = admin)
     * @return "null" if authenticateRole failed. An actual user is returned if 
     *                 this method is successful; 
     */
    public User authenticateRole(User user, int role)
    {
        try
        {
            // System.out.println("Ran authenticateUser");
            /* create the query */
            final PreparedStatement statement = connect().prepareStatement(
                    "SELECT * FROM `roles` WHERE `ssn`='" +user.getSSN()+ "'");

            /* Creating a variable to execute query */
            final ResultSet result = statement.executeQuery();
            int rolefound = 0;
            boolean[] boolArr = new boolean[4];
            boolArr[0]= false;
            boolArr[1]= false;
            boolArr[2]= false;
            boolArr[3]= false;
            /* Role Table: 1=ssn and 2=role */
            while (result.next()) 
            {
                int temp = result.getInt(2);// gets second column
                /* populate entire array */
                if (temp == 1)
                {
                    boolArr[0] = true;
                }
                else if (temp == 2)
                {
                    boolArr[1] = true;
                }
                else if (temp == 3)
                {
                    boolArr[2] = true;
                }
                else if (temp == 4)
                {
                    boolArr[3] = true;
                }
            }
            /* Check array now for role */
            if (boolArr[(role-1)])
            {
                user.setRoles(boolArr);
                rolefound = 1;
            }
            System.out.println("Daranh's Roles: "+boolArr[0]+""+boolArr[1]+""+boolArr[2]+""+boolArr[3]);
            if (rolefound == 1)
            {
                return user;
            }
            else return null;
        }
        catch(Exception e)
        {
            System.out.println("Exception at MyQuery:authenticateRole");
            myLogger.info("authenticate role fail");
            return null;
        }
    }



    /**
     * Use this method to return a user matching the SSN provided
     * 
     * @param ssn
     * @return User -- The User class will be returned with all its attributes
     *         gathered from the database
     */

    public User loadUser(int ssn) 
    {
        User tempuser = null;
        // System.out.println("Ran loadUser");
        try
        {
            final PreparedStatement test = connect().prepareStatement("SELECT * FROM `CS2340`.`patient` WHERE `ssn`='" + ssn + "'");
            final ResultSet result = test.executeQuery();

            while (result.next())
            {
                final PreparedStatement statement = connect().prepareStatement(
                        "SELECT * FROM `CS2340`.`patient` WHERE `ssn` LIKE '" + ssn + "'");

                /* Creating a variable to execute query */
                final ResultSet results = statement.executeQuery();
                int temp=0;

                while (results.next()) 
                {
                    temp = result.getInt(13);
                    System.out.println(temp);

                    /*
                     * getString returns the data. Below are patient attributes. 1 =
                     * password 9 = name 2 = username 10 = address 3 = pharmacy 11 =
                     * email 4 = birthdate 12 = suspend 5 = cellphone 13 = ssn 6 = phone
                     * 7 = insurance 8 = gender
                     */
                }
                tempuser = createUserBySSN(temp);

                /*Adds all treatment records to the user class*/
                final PreparedStatement anothertest = connect().prepareStatement("SELECT * FROM `CS2340`.`treatment_record` WHERE `ssn`='" + ssn + "'");
                final ResultSet anotherresult = anothertest.executeQuery();
                while (anotherresult.next())
                {
                    int trID = Integer.parseInt(anotherresult.getString(14));
                    System.out.println("Line 593:" + trID );
                    TreatmentRecord temptr = createTreatmentRecord(trID);
                    System.out.println("Line 595:" + temptr );
                    tempuser.addTreatmentRecord(temptr);
                    System.out.println("Line 597:" + tempuser );
                }
                return tempuser;
            }    
        }
        catch(SQLException e) 
        {
            System.out.println("Exception at line 593 of Query Class");
            myLogger.info("SQL exception");
            return null;
        }
        return tempuser;


    }

    /**
     * You must specify the username first. Usernames are unique
     * 
     * @param username
     * @return int - this will be the patients SSN with no dashes or spaces
     *         between the numbers 
     */
    public int getSSN(String username)
    {
        // System.out.println("Ran getSSN");
        // create the query
        try
        {
            final PreparedStatement statement = connect().prepareStatement(
                    "SELECT * FROM `patient` WHERE `username` LIKE '" + username
                    + "'");

            // Creating a variable to execute query
            final ResultSet result = statement.executeQuery();

            int socialNum = 0;

            while (result.next()) {
                socialNum = Integer.parseInt(result.getString(13));
                /*
                 * getString returns the data. Below are patient attributes. 1 =
                 * password 9 = name 2 = username 10 = address 3 = pharmacy 11 =
                 * email 4 = birthdate 12 = suspend 5 = cellphone 13 = ssn 6 = phone
                 * 7 = insurance 8 = gender
                 */
            }
            return socialNum;
        }
        catch(SQLException e)
        {
            System.out.println("getSSN SQL Exception");
            return (Integer) null;
        }

    }

    /**
     * Creates a User object. Use this method when accessing User Information from the 
     * database A Nurse or Doctor will use this method whenever he pulls up information on a patient
     * 
     * @param username
     * @return User 
     */
    private User createUser(String username) 
    {
        final User user = new User();
        try{
            // System.out.println("Ran createUser");

            int tempSSN = 0;

            tempSSN = getSSN(username);

            final String a1 = getUserAttributes(tempSSN,1); /* password */ 
            final String a2 = getUserAttributes(tempSSN,2); /* username */
            final String a3 = getUserAttributes(tempSSN,3); /* pharmacy */
            final Date a4 = getPatientBday(tempSSN,4); /* birthdate */
            final String a5 = getUserAttributes(tempSSN,5); /* cellphone */
            final String a6 = getUserAttributes(tempSSN,6); /* phone */
            final String a7 = getUserAttributes(tempSSN,7); /* insurance */
            final String a8 = getUserAttributes(tempSSN,8); /* gender */
            final String a9 = getUserAttributes(tempSSN,9); /* name */
            final String a10 = getUserAttributes(tempSSN,10); /* address */
            final String a11 = getUserAttributes(tempSSN,11); /* email */
            final String a12 = getUserAttributes(tempSSN,12); /* suspend */
            final String a13 = getUserAttributes(tempSSN,13); /* ssn */

            user.setPassword(a1);
            user.setUserName(a2);
            user.setPharmacy(a3);
            user.setBirthDate(a4);
            user.setCellPhone(a5);
            user.setPhone(a6);
            user.setInsurance(a7);
            /* Convert String enum to true java Enum */
            if (a8.equals("Male")) {
                user.setGender(Gender.MALE);
            } else{
                user.setGender(Gender.FEMALE);
            }

            user.setName(a9);
            user.setAddress(a10);
            user.setEmail(a11);

            user.setSuspended(convertStringToBoolean(a12));

            user.setSSN(Integer.parseInt(a13));
            return user;
        }
        catch(Exception e)
        {
            System.out.println("Exception in CreateUser in Query Class");
            myLogger.info("Create user fails");
            return user;
        }
    }

    /**
     * Call this method to if you have the patients ssn number to retrieve a user class
     * @param ssn - patient social security number in integer form
     * @return User - return a user
     */
    private User createUserBySSN(int ssn) 
    {

        // System.out.println("Ran createUser");
        final User user = new User();
        int tempSSN = 0;

        tempSSN = ssn;

        final String a1 = getUserAttributes(tempSSN, 1); /* password */
        final String a2 = getUserAttributes(tempSSN, 2); /* username */
        final String a3 = getUserAttributes(tempSSN, 3); /* pharmacy */
        final Date a4 = getPatientBday(tempSSN, 4); /* birthdate */
        final String a5 = getUserAttributes(tempSSN, 5); /* cellphone */
        final String a6 = getUserAttributes(tempSSN, 6); /* phone */
        final String a7 = getUserAttributes(tempSSN, 7); /* insurance */
        final String a8 = getUserAttributes(tempSSN, 8); /* gender */
        final String a9 = getUserAttributes(tempSSN, 9); /* name */
        final String a10 = getUserAttributes(tempSSN, 10);/* address */
        final String a11 = getUserAttributes(tempSSN, 11);/* email */
        final String a12 = getUserAttributes(tempSSN, 12);/* suspend */
        final String a13 = getUserAttributes(tempSSN, 13);/* ssn */

        user.setPassword(a1);
        user.setUserName(a2);
        user.setPharmacy(a3);

        user.setBirthDate(a4);
        user.setCellPhone(a5);
        user.setPhone(a6);
        user.setInsurance(a7);
        /* Convert String enum to true java Enum */
        if (a8.equals("Male")) 
        {
            user.setGender(Gender.MALE);
        } 
        else
        {
            user.setGender(Gender.FEMALE);
        }

        user.setName(a9);
        user.setAddress(a10);
        user.setEmail(a11);

        user.setSuspended(convertStringToBoolean(a12));

        System.out.println("Social Security: " + a13);
        user.setSSN(Integer.parseInt(a13));
        return user;
    }

    /**
     * Will return a string of patient attributes space delimited. 
     * @param ssn - Social Security Number
     * @param attributeNumber - Database column number
     * @return appInfo - A string of appointment attributes
     */
    private static String getAppAttributes(int ssn, int attributeNumber)
    {
        try{
            // System.out.println("Ran getAppAttributes");
            // create the query
            final PreparedStatement statement = connect().prepareStatement(
                    "SELECT * FROM appointment WHERE ssn=" + ssn);

            // Creating a variable to execute query
            final ResultSet result = statement.executeQuery();

            String appInfo = "";

            while (result.next()) {
                switch (result.getMetaData().getColumnType(attributeNumber)) {
                case java.sql.Types.DATE:

                    DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");

                    appInfo = formatter.format(result.getDate(attributeNumber));

                    break;
                default:
                    appInfo = result.getString(attributeNumber);
                }
            }
            return appInfo;
        }
        catch(Exception e)
        {
            System.out.println("Exception at MyQuery: getAppAttributes");
            return null;
        }
    }

    /**
     * Use this method to convert string to boolean
     * @param booleanString - String
     * @return boolean
     */
    private boolean convertStringToBoolean(String booleanString) {
        // System.out.println("Ran convertStringToBoolean");
        final String strBoolean = booleanString;

        /* Do the String to boolean conversion */
        final boolean theValue = Boolean.parseBoolean(strBoolean);

        return theValue;
    }

    /**
     * Main
     * @param args
     */
    public static void main(String[] args) 
    {
        try 
        {

            //        User steve1;
            //        final Date date = new Date(20110409);
            //
            //final boolean[] arr = { true, false, false, false };
            MyQuery newquery = new MyQuery();
            int ssn = 901901901;
            User newUser = newquery.loadUser(ssn);
            //PatientInformation pi = newUser.getPatientInfo();
            //List<TreatmentRecord> tr = pi.getTreatmentRecords();
            System.out.println ("Line 850:" + newUser);
            System.out.println
            (
                    "Password: " + newUser.getPassword() + "\n" +
                    "Username: " + newUser.getUserName() + "\n" +
                    "Pharmacy: " + newUser.getPharmacy() + "\n" +
                    "birthdate: " + newUser.getBirthDate() + "\n" +
                    "cellphone: " + newUser.getCellPhone() + "\n" +
                    "phone: " + newUser.getPhone() + "\n" +
                    "insurance" + newUser.getInsurance() + "\n" +
                    "gender" + newUser.getGender() + "\n" +
                    "Name" + newUser.getName() + "\n" +
                    "Address" + newUser.getAddress() + "\n" +
                    "email" + newUser.getEmail() + "\n" +
                    "suspended" + newUser.getSuspended() + "\n" +
                    "SSN" + newUser.getSSN() + "\n" +
                    "Treatment Record Stuff: "

            );           
            List<TreatmentRecord> tr= newUser.getTreatmentRecords();
            System.out.println("Treatment Record: " + tr.size());
            TreatmentRecord onetr = tr.get(1);
            System.out.println("Before");
            System.out.println("HEIGHT:  ZZZZZZZZ" + onetr);
            System.out.println("After");
            TreatmentRecord newtr = null;
            Iterator<TreatmentRecord> iterator = null;
            iterator = (newUser.getTreatmentRecords()).iterator();
            while(iterator.hasNext())
            {
                newtr = iterator.next();
                int count = 1;
                System.out.println
                (
                        "TreatmentRecord #" + count + ": " +
                        "Diagnosis: " + newtr.getDiagnosis() + "\n" +
                        "Temperature: " + newtr.getTemperature() + "\n" +
                        "Height: " + newtr.getHeight() + "\n" +
                        "Time: " + newtr.getTime() + "\n" +
                        "Nurse: " + newtr.getAttendingNurse() + "\n" +
                        "Symptoms: " + newtr.getSymptoms() + "\n" +
                        "diastolic: " + newtr.getBpDiastolic() + "\n" +
                        "systolic: " + newtr.getBpSystolic() + "\n" +
                        "doctor: " + newtr.getTreatingDoctor() + "\n" +
                        "pulse: " + newtr.getPulse() + "\n" +
                        "weight: " + newtr.getWeight() + "\n" +
                        "date: " + newtr.getDate() + "\n"                 
                );    
                count++;
            }
            User secUser = newquery.authenticateRole(newUser,1);
            System.out.println("Users Birthday"+secUser.getBirthDate());
            

        }
        catch(Exception e)
        {
            System.out.println("Main Failed");

        }

    }

    /**
     * Use this method ONLY if you're accessing an attribute with a DATE DATATYPE.
     * 
     * @param ssn - Social Security Number
     * @param attributeNumber - column number according to database
     * @return Date - Date of appointment
     */
    private Date getAppDate(int ssn, int attributeNumber) 
    {
        try{
            //System.out.println("Ran getAppDate");
            // create the query
            final PreparedStatement statement = connect().prepareStatement(
                    "SELECT * FROM appointment WHERE ssn=" + ssn);

            // Creating a variable to execute query
            final ResultSet result = statement.executeQuery();

            Date date = null;

            while (result.next()) {
                switch (result.getMetaData().getColumnType(attributeNumber)) {
                case java.sql.Types.DATE:
                    date = result.getDate(attributeNumber);
                    break;
                default:
                    date = null;
                    break;
                }

            }
            return date;
        }
        catch(Exception e)
        {
            System.out.println("Exception at MyQuery: getAppDate");
            return null;
        }
    }

    /**
     * Works like the loaduser method. All attributes of the Appointment will be stored 
     * in attributes variables and returned to the caller.
     * @param ssn - SSN 
     * @param date -Date of appointment
     * @return Appointment class
     */
    public Appointment loadAppointment(int ssn, Date date)  
    {
        try
        {
            // System.out.println("Ran loadAppointment");

            final PreparedStatement statement = connect().prepareStatement(
                    "SELECT * FROM `appointment` WHERE `ssn` = '" + ssn
                    + "' AND `date` = '" + date + "'");

            /* Creating a variable to execute query */
            final ResultSet result = statement.executeQuery();
            String dbSSN = "";

            while (result.next()) 
            {
                dbSSN = result.getString(1);
                /*
                 * Below are appointment attributes. 1 = ssn 5 = reason 2 = date 6 =
                 * name 3 = timeStart 7 = timeEnd 4 = doctor 8 = patient
                 */
            }
            return createAppointment(dbSSN);
        }
        catch(SQLException e)
        {
            System.out.println("SQL Exception at MyQuery: loadAppointment");
            return null;
        }
    }

    /**
     * Use this method when you have a name and need all the appointments
     * associated with this name in an Arraylist
     * 
     * @param name
     * @return List<Appointment>
     */
    public List<Appointment> searchUserAppointments(int ssn)
    {
        try{
            // System.out.println("SELECT * FROM `CS2340`.`appointment` WHERE `name` LIKE "
            // + "'"+name+"' LIMIT 0 , 30");
            // create the query
            final PreparedStatement statement = connect().prepareStatement(
                    "SELECT * FROM `CS2340`.`appointment` WHERE `ssn`=" + "'"
                    + ssn + "' LIMIT 0 , 30");

            // Creating a variable to execute query
            final ResultSet result = statement.executeQuery();

            final List<Appointment> appInfo = new ArrayList<Appointment>();

            while (result.next()) {
                Appointment tempApp = createAppointment(result.getString(6));
                appInfo.add(tempApp);
                /*
                 * Below are appointment attributes. 1 = ssn 5 = reason 2 = date 6 =
                 * name 3 = timeStart 7 = timeEnd 4 = doctor 8 = patient
                 */
            }
            return appInfo;
        }
        catch(Exception e)
        {
            System.out.println("Exception at MyQuery: searchUserAppointment");
            return null;
        }
    }

    /**
     * Inserts an appointment into the database
     * @param ssn - SSN 
     * @param date - Date object
     * @param tr - Treatmentrecord object
     */
    public void addTreatmentRecord(int ssn, Date date, TreatmentRecord tr) {
        // System.out.println("Ran addAppointment");
        Statement stmt = null;

        /**/
        final String diagnosis = tr.getDiagnosis(); /*  */
        final double temperature = tr.getTemperature(); /*  */
        final Height height = tr.getHeight(); /*  */
        final Time time = tr.getTime(); /*  */
        final String nurse = tr.getAttendingNurse(); /*  */
        final String symptoms = tr.getSymptoms(); /*  */
        final int diastolic = tr.getBpDiastolic(); /*  */
        final int systolic = tr.getBpSystolic(); /*  */
        final String doctor = tr.getTreatingDoctor(); /*  */
        final int pulse = tr.getPulse(); /*  */
        final double weight = tr.getWeight(); /*  */
        /*
         * Below are appointment attributes. 1 = ssn 5 = reason 2 = date 6 =
         * name 3 = timeStart 7 = timeEnd 4 = doctor 8 = patient
         */
        try {
            stmt = connect().createStatement();

            stmt.executeUpdate("INSERT INTO `CS2340`.`treatment_record` "
                    + "(`ssn`, `diagnosis`, `temperature`, `height`, `time`,"
                    + " `attending_nurse`, `symptoms`, `bp_diastolic`, `bp_systolic`," +
                    " `treating_doctor`, `pulse`, `weight`, `date`) "
                    + "VALUES " + "('" + ssn + "', '" + diagnosis + "', '"
                    + temperature + "', '" + height + "', '" + time + "', '"
                    + nurse + "', '" + symptoms + "', '" + diastolic + "', '" 
                    + systolic +"', '" + doctor + "', '" + pulse + "', '"+weight+ "', '"
                    + date + "')");
        } 
        catch (SQLException e) 
        {
            System.out.println("Exception at MyQuery: addTreatmentRecord");
        } 
        catch (Exception e) 
        {
            System.out.println("Exception at MyQuery: addTreatmentRecord");
        }
    }

    /**
     * Gives you a List of treatment records given a users social security number
     * @param ssn
     * @return List of treamtment records
     */
    public List<TreatmentRecord> getTreatmentRecords(int ssn)
    {
        try
        {

            // create the query
            final PreparedStatement statement = connect().prepareStatement(
                    "SELECT * FROM `CS2340`.`treatment_record` WHERE `ssn`=" + "'"
                    + ssn + "' LIMIT 0 , 30");

            // Creating a variable to execute query
            final ResultSet result = statement.executeQuery();
            // constructs a new empty ArrayList
            final List<TreatmentRecord> trInfo = new ArrayList<TreatmentRecord>();

            while (result.next()) {
                TreatmentRecord temptr = createTreatmentRecord(result.getInt(14));
                trInfo.add(temptr);
            }
            return trInfo;
        }
        catch (Exception e)
        {
            System.out.println("Exception at MyQuery getTreatmentRecords");
            return null;
        }
    }

    /**
     * Private method to return the attributes of a treatment record in string format
     * @param trID - Treatment Record ID
     * @param attributeNumber - Attribute number
     * @return String of attributes
     */
    private static String gettrAttributes(int trID, int attributeNumber)
    {
        try{
            // System.out.println("Ran getAppAttributes");
            // create the query
            final PreparedStatement statement = connect().prepareStatement(
                    "SELECT * FROM treatment_record WHERE treat_recordID=" + trID);

            // Creating a variable to execute query
            final ResultSet result = statement.executeQuery();

            String trInfo = "";

            while (result.next()) {
                switch (result.getMetaData().getColumnType(attributeNumber)) {
                case java.sql.Types.DATE:

                    DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");

                    trInfo = formatter.format(result.getDate(attributeNumber));

                    break;
                default:
                    trInfo = result.getString(attributeNumber);
                }
            }
            return trInfo;
        }
        catch (Exception e)
        {
            System.out.println("Exception at MyQuery: gettrAttributes");
            return null;
        }
    }

    /**
     * Gets the time of the treatment record
     * @param trID - Treatment Record ID
     * @param attributeNumber
     * @return Time - the time of the treatment record
     */
    private Time gettrTime(int trID, int attributeNumber)
    {
        try{
            // System.out.println("Ran getAppTime");
            // create the query
            final PreparedStatement statement = connect().prepareStatement(
                    "SELECT * FROM treatment_record WHERE treat_recordID=" + trID);

            // Creating a variable to execute query
            final ResultSet result = statement.executeQuery();

            Time time = null;

            while (result.next()) {
                switch (result.getMetaData().getColumnType(attributeNumber)) 
                {
                case java.sql.Types.TIME:
                    time = result.getTime(attributeNumber);
                    break;
                default:
                    time = null;
                    break;
                }
            }
            return time;
        }
        catch(Exception e)
        {
            System.out.println("Exception at MyQuery: gttrTime");
            return null;
        }
    }

    /**
     * Use this to get the date of the treatment record
     * @param trID - Treatment ID number - All treatment records have ID number
     * @param attributeNumber - column number
     * @return Date - returns a date object
     * @throws Exception
     */
    private Date gettrDate(int trID, int attributeNumber)
    {
        try
        {
            //System.out.println("Ran getAppDate");
            // create the query
            final PreparedStatement statement = connect().prepareStatement(
                    "SELECT * FROM treatment_record WHERE treat_recordID=" + trID);

            // Creating a variable to execute query
            final ResultSet result = statement.executeQuery();

            Date date = null;

            while (result.next()) {
                switch (result.getMetaData().getColumnType(attributeNumber)) {
                case java.sql.Types.DATE:
                    date = result.getDate(attributeNumber);
                    break;
                default:
                    date = null;
                    break;
                }

            }
            return date;
        }
        catch(Exception e)
        {
            System.out.println("Exception at MyQuery: gettrDate");
            return null;
        }
    }

    /**
     * Given a treatment record ID number, this method will search in the database 
     * and gather all attributes for that treatment record and return a treatment record
     * @param trID - Treatment record ID number
     * @return TreatmentRecord - Returns a treatment record object
     */
    private TreatmentRecord createTreatmentRecord(int trID) 
    {
        try
        {
            // System.out.println("Ran createTreatmentRecord");
            final String a2 = gettrAttributes(trID, 2); /* diagnosis */      
            final String a3 = gettrAttributes(trID, 3); /* temperature */
            final String a4 = gettrAttributes(trID, 4); /* height */
            final Time a5 = gettrTime(trID, 5); /* time */
            final String a6 = gettrAttributes(trID, 6); /* attending nurse */
            final String a7 = gettrAttributes(trID, 7); /* symptoms */
            final String a8 = gettrAttributes(trID, 8); /* bp_diastolic */
            final String a9 = gettrAttributes(trID, 9); /* bp_systolic */
            final String a10 = gettrAttributes(trID, 10); /* treating_doctor */
            final String a11 = gettrAttributes(trID, 11); /* pulse */
            final String a12 = gettrAttributes(trID, 12); /* weight */
            final Date a13 = gettrDate(trID, 13); /* date */

            final TreatmentRecord tr = new TreatmentRecord();
            tr.setDiagnosis(a2);
            tr.setTemperature(Double.parseDouble(a3));
            tr.setHeight(HeightMessageEditor.getHeight(a4));
            tr.setTime(a5);
            tr.setAttendingNurse(a6);
            tr.setSymptoms(a7);
            tr.setBpDiastolic(Integer.parseInt(a8));
            tr.setBpSystolic(Integer.parseInt(a9));
            tr.setTreatingDoctor(a10);
            tr.setPulse(Integer.parseInt(a11));
            tr.setWeight(Double.parseDouble(a12));
            tr.setDate(a13);

            return tr;
        }
        catch (Exception e)
        {
            System.out.println("Exception MyQuery: createTreatmentRecord");
            return null;
        }
    }

    /**
     * Returns a list of appointments for that date
     * @param date - date user is searching by
     * @return List<Appointment> 
     */
    public List<Appointment> searchUserAppointmentDateOnly(Date date)
    {
        try
        {
            // create the query
            final PreparedStatement statement = connect().prepareStatement(
                    "SELECT * FROM `CS2340`.`appointment` WHERE `date`='" +
                    date +"' LIMIT 0 , 30");

            // Creating a variable to execute query
            final ResultSet result = statement.executeQuery();
            // System.out.println(result);
            // constructs a new empty ArrayList

            final List<Appointment> appInfo = new ArrayList<Appointment>();

            while (result.next()) {
                Appointment tempApp = createAppointment(result.getString(6));
                appInfo.add(tempApp);
                /*
                 * Below are appointment attributes. 1 = ssn 5 = reason 2 = date 6 =
                 * name 3 = timeStart 7 = timeEnd 4 = doctor 8 = patient
                 */
            }
            return appInfo;
        }
        catch(SQLException s)
        {
            System.out.println("Exception at MyQuery: searchUserAppointmentDateOnly");
            return null;
        }
    }

    /**
     * Search for appointment that match the date and ssn number
     * @param ssn - SSN Number
     * @param date - Date type
     * @return a list of appointments. 
     */
    public List<Appointment> searchUserAppointmentDate(int ssn, Date date)

    {
        try{
            // System.out.println("SELECT * FROM `CS2340`.`appointment` WHERE `name` LIKE "
            // + "'"+name+"' LIMIT 0 , 30");
            // create the query
            final PreparedStatement statement = connect().prepareStatement(
                    "SELECT * FROM `CS2340`.`appointment` WHERE `ssn`=" + "'"
                    + ssn + "' AND '" + "`date`='" + date +"' LIMIT 0 , 30");


            // Creating a variable to execute query
            final ResultSet result = statement.executeQuery();
            // System.out.println(result);
            // constructs a new empty ArrayList

            final List<Appointment> appInfo = new ArrayList<Appointment>();

            while (result.next()) {
                Appointment tempApp = createAppointment(result.getString(6));
                appInfo.add(tempApp);
                /*
                 * Below are appointment attributes. 1 = ssn 5 = reason 2 = date 6 =
                 * name 3 = timeStart 7 = timeEnd 4 = doctor 8 = patient
                 */
            }
            return appInfo;
        }
        catch(Exception e)
        {
            System.out.println("Exception at MyQuery: searchUserAppointmentDate");
            return null;
        }
    }



    /**
     * Use this method ONLY if you're accessing an attribute with a TIME DATATYPE.
     * @param ssn - SSN
     * @param attributeNumber - Column number
     * @return Time 
     */
    private Time getAppTime(int ssn, int attributeNumber) 
    {
        try{
            // System.out.println("Ran getAppTime");
            // create the query
            final PreparedStatement statement = connect().prepareStatement(
                    "SELECT * FROM appointment WHERE ssn=" + ssn);

            // Creating a variable to execute query
            final ResultSet result = statement.executeQuery();

            Time time = null;

            while (result.next()) {
                switch (result.getMetaData().getColumnType(attributeNumber)) {
                case java.sql.Types.TIME:
                    time = result.getTime(attributeNumber);
                    break;
                default:
                    time = null;
                    break;
                }

            }
            return time;
        }
        catch(Exception e)
        {
            System.out.println("Exception at MyQuery: getAppTime");
            return null;
        }
    }

    /**
     * Create one appointment instance for you
     * @param username - Username patient
     * @return Appointment object for the username
     */
    private Appointment createAppointment(String username) 
    {
        try{
            // System.out.println("Ran createAppointment");

            int tempSSN = 0;

            tempSSN = getSSN(username);

            final Date a2 = getAppDate(tempSSN, 2); /* date */
            final Time a3 = getAppTime(tempSSN, 3); /* timeStart */
            final String a4 = getAppAttributes(tempSSN, 4); /* doctor */
            final String a5 = getAppAttributes(tempSSN, 5); /* reason */
            final String a6 = getAppAttributes(tempSSN, 6); /* name */
            final Time a7 = getAppTime(tempSSN, 7); /* timeEnd */
            final String a8 = getAppAttributes(tempSSN, 8); /* patient */

            final Appointment app = new Appointment(tempSSN, a2, a3, a7);
            app.setDate(a2);
            app.setTimeStart(a3);
            app.setDoctor(a4);
            app.setReason(a5);
            app.setPatient(a6);
            app.setTimeEnd(a7);
            app.setPatient(a8);

            return app;
        }
        catch (Exception e)
        {
            return null;
        }
    }

    /**
     * Use this method
     * to add a new appointment. User parameter must have a SSN and Name in the
     * database. Patient must already have a user profile in the database.
     * 
     * @param user  - user instance
     * @param app - appointment instance
     * @return boolean - true for success appointment addition to database
     */
    public boolean addAppointment(int ssn, Appointment app) {
        // System.out.println("Ran addAppointment");
        Statement stmt = null;
        
        final Date appDate = app.getDate();
        // Date appDate = user.getBirthDate();
        final Time timeStart = app.getTimeStart();
        final String doctor = app.getDoctor();
        final String reason = app.getReason();
        final String name = app.getPatient();
        /* String name = "John"; */
        final Time timeEnd = app.getTimeEnd();
        final String patient = app.getPatient();

        try {
            stmt = connect().createStatement();

            stmt.executeUpdate("INSERT INTO `CS2340`.`appointment` "
                    + "(`ssn`, `date`, `timeStart`, `doctor`, `reason`,"
                    + " `name`, `timeEnd`, `patient`) "
                    + "VALUES " + "('" + ssn + "', '" + appDate + "', '"
                    + timeStart + "', '" + doctor + "', '" + reason + "', '"
                    + name + "', '" + timeEnd + "', '" + patient + "')");

            /*
             * Below are appointment attributes. 1 = ssn 5 = reason 2 = date 6 =
             * name 3 = timeStart 7 = timeEnd 4 = doctor 8 = patient
             */
            return true;
        } catch (SQLException e) 
        {
            System.out.println("SQL Exception in addAppointment");
            return false;
        } catch (Exception e) 
        {
            System.out.println("Exception in addAppointment");
            return false;
        }
    }

    /**
     * Simply deletes an appointment, must give date and ssn
     * @param ssn - SSN
     * @param date - Date
     */
    public void deleteAppointment(int ssn, Date date) 
    {
        Statement stmt = null;
        try{

            // System.out.println("Ran deleteUser");

            final boolean confirmation = true;

            /*
             * DELETE FROM `CS2340`.`appointment` WHERE `appointment`.`ssn` =
             * '111111111' AND `appointment`.`date` = '0000-00-00'
             */

            stmt = connect().createStatement();
            stmt.executeUpdate("DELETE FROM `CS2340`.`appointment`"
                    + " WHERE `appointment`.`ssn` = '"
                    + ssn + "' AND `appointment`.`date` = '" + date + "'");
            stmt = connect().createStatement();
            stmt.close();

        } 
        catch(SQLException e)
        {
            System.out.println("Delete did not occur");
        }
    }



    /**
     * Update an appointment using a user and the appointment instance
     * @param ssn - SSN
     * @param app - Appointment object
     */
    public void updateAppointment(int ssn, Appointment app)
    {
        try
        {
            //System.out.println("Ran updateUser");
            Statement stmt = null;

            final Date appDate = app.getDate();
            // Date appDate = user.getBirthDate();
            final Time timeStart = app.getTimeStart();
            final String doctor = app.getDoctor();
            final String reason = app.getReason();
            final String name = app.getPatient();
            /* String name = "John"; */
            final Time timeEnd = app.getTimeEnd();
            final String patient = app.getPatient();


            stmt = connect().createStatement();
            stmt.executeUpdate("UPDATE  `CS2340`.`appointment` SET  "
                    + "`ssn` =  '"
                    + ssn
                    + "', "
                    + "`date` =  '"
                    + appDate
                    + "', "
                    + "`timeStart` =  '"
                    + timeStart
                    + "',"
                    + "`doctor` =  '"
                    + doctor
                    + "',"
                    + "`reason` =  '"
                    + reason
                    + "',"
                    + "`name` =  '"
                    + name
                    + "',"
                    + "`timeEnd` =  '"
                    + timeEnd
                    + "',"
                    + "`patient` =  '"
                    + patient
                    + "',"
                    + "WHERE `appointment`.`ssn` = "
                    + ssn
                    + "AND `appointment`.`date` = " + appDate);

            stmt = connect().createStatement();
            stmt.close();
        }
        catch(Exception e)
        {
            System.out.println("Exception at MyQuery: updateAppointment");
        }
    }
}